using System;
using System.Data.SqlClient;
using System.Text;
using System.Text.RegularExpressions;

using ObjectRelationalMapping.Helper;
using ObjectRelationalMapping.Model;

namespace ObjectRelationalMapping
{
    internal static partial class Mapper
    {
        internal static class Database
        {
            internal static void Generate ( )
            {
                if ( Common.EntityCollection.Count > 0 )
                {
                    Console.Write ( "Generating the database objects..." );
                    int numberOfDatabaseObjects = 0;

                    foreach ( Entity entity in Common.EntityCollection )
                    {
                        StringBuilder temporaryTemplate =
                            new StringBuilder ();
                        temporaryTemplate.Append (
                            GenerateDeleteStatement ( entity ) );
                        temporaryTemplate.Append (
                            GenerateInsertStatement ( entity ) );
                        temporaryTemplate.Append (
                            GeneratePagingStatement ( entity ) );
                        temporaryTemplate.Append (
                            GenerateSelectStatement ( entity ) );
                        temporaryTemplate.Append (
                            GenerateUpdateStatement ( entity ) );
                        entity.DatabaseTemplate =
                            temporaryTemplate.ToString ();
                        temporaryTemplate = null;

                        numberOfDatabaseObjects += 6;
                    }

                    Console.WriteLine (
                        "and {0} database objects for were generated.",
                        numberOfDatabaseObjects );
                }
            }

            private static string GenerateColumns (
                bool isReadStatement,
                int numberOfProperties,
                PropertyCollection<Metadata> properties )
            {
                StringBuilder result = new StringBuilder ();

                numberOfProperties -= 1;
                for ( int index = 0; index <= numberOfProperties; ++index )
                {
                    Property<Metadata> property = properties[ index ];
                    if ( isReadStatement )
                    {
                        if ( index != 0 )
                        {
                            result.Append ( "       " );
                        }
                    }
                    else
                    {
                        result.Append ( "         " );
                    }
                    result.Append ( "[" );
                    result.Append ( property.Name );
                    result.Append ( "]" );
                    if ( index < numberOfProperties
                        && numberOfProperties != 0 )
                    {
                        result.Append ( ",\r\n" );
                    }
                }

                return result.ToString ();
            }

            private static string GenerateConditionClause (
                bool isNullable,
                int numberOfProperties,
                PropertyCollection<Metadata> properties )
            {
                StringBuilder result = new StringBuilder ();

                numberOfProperties -= 1;
                for ( int index = 0; index <= numberOfProperties; ++index )
                {
                    Property<Metadata> property = properties[ index ];
                    if ( isNullable )
                    {
                        result.Append ( " (" );
                    }
                    result.Append ( " [" );
                    result.Append ( property.Name );
                    result.Append ( "] = @" );
                    result.Append ( property.Name );
                    if ( isNullable )
                    {
                        result.Append ( "\r\n" );
                        result.Append ( "    OR @" );
                        result.Append ( property.Name );
                        result.Append ( " IS NULL )" );
                    }
                    if ( index < numberOfProperties
                        && numberOfProperties != 0 )
                    {
                        result.Append ( "\r\n" );
                        result.Append ( "   AND" );
                    }
                }

                return result.ToString ();
            }

            private static string GenerateDeleteStatement ( Entity entity )
            {
                string deleteStatementTemplate = string.Empty;

                deleteStatementTemplate =
                    Regex.Replace (
                        Common.TemplateForDatabaseDelete,
                        Common.Constants.UserDefinedClassName,
                        entity.ClassName );
                deleteStatementTemplate =
                    Regex.Replace (
                        deleteStatementTemplate,
                        Common.Constants.UserDefinedPrimaryKey,
                        GenerateConditionClause (
                            false,
                            entity.Metadata.Properties.CountOfPrimaryKeyProperties,
                            entity.Metadata.Properties.PrimaryKeyProperties ) );
                deleteStatementTemplate =
                    Regex.Replace (
                        deleteStatementTemplate,
                        Common.Constants.UserDefinedName,
                        entity.Name );
                deleteStatementTemplate =
                    Regex.Replace (
                        deleteStatementTemplate,
                        Common.Constants.UserDefinedParameter,
                        GenerateParameters (
                            false,
                            entity.Metadata.Properties.CountOfPrimaryKeyProperties,
                            entity.Metadata.Properties.PrimaryKeyProperties ) );

                return deleteStatementTemplate;
            }

            private static string GenerateExpressions (
                int numberOfProperties,
                PropertyCollection<Metadata> properties )
            {
                StringBuilder result = new StringBuilder ();

                numberOfProperties -= 1;
                for ( int index = 0; index <= numberOfProperties; ++index )
                {
                    Property<Metadata> property = properties[ index ];
                    result.Append ( " [" );
                    result.Append ( property.Name );
                    result.Append ( "] = @" );
                    result.Append ( property.Name );
                    if ( index < numberOfProperties
                        && numberOfProperties != 0 )
                    {
                        result.Append ( ",\r\n      " );
                    }
                }

                return result.ToString ();
            }

            private static string GenerateInsertStatement ( Entity entity )
            {
                string insertStatementTemplate = string.Empty;

                insertStatementTemplate =
                    Regex.Replace (
                        Common.TemplateForDatabaseInsert,
                        Common.Constants.UserDefinedClassName,
                        entity.ClassName );
                insertStatementTemplate =
                    Regex.Replace (
                        insertStatementTemplate,
                        Common.Constants.UserDefinedColumn,
                        GenerateColumns (
                            false,
                            entity.Metadata.Properties.CountOfNonPrimaryKeyProperties,
                            entity.Metadata.Properties.NonPrimaryKeyProperties ) );
                insertStatementTemplate =
                    Regex.Replace (
                        insertStatementTemplate,
                        Common.Constants.UserDefinedName,
                        entity.Name );
                insertStatementTemplate =
                    Regex.Replace (
                        insertStatementTemplate,
                        Common.Constants.UserDefinedParameter,
                        GenerateParameters (
                            false,
                            entity.Metadata.Properties.CountOfNonPrimaryKeyProperties,
                            entity.Metadata.Properties.NonPrimaryKeyProperties ) );
                insertStatementTemplate =
                    Regex.Replace (
                        insertStatementTemplate,
                        Common.Constants.UserDefinedValue,
                        GenerateValues (
                            entity.Metadata.Properties.CountOfNonPrimaryKeyProperties,
                            entity.Metadata.Properties.NonPrimaryKeyProperties ) );

                return insertStatementTemplate;
            }

            private static string GeneratePagingStatement ( Entity entity )
            {
                string pagingStatementTemplate = string.Empty;

                if ( entity.Metadata.Properties.CountOfPrimaryKeyProperties > 0 )
                {
                    pagingStatementTemplate =
                         Regex.Replace (
                             Common.TemplateForDatabasePaging,
                             Common.Constants.UserDefinedClassName,
                             entity.ClassName );
                    pagingStatementTemplate =
                        Regex.Replace (
                            pagingStatementTemplate,
                            Common.Constants.UserDefinedColumn,
                            GenerateColumns (
                                true,
                                entity.Metadata.Properties.Count,
                                entity.Metadata.Properties ) );
                    pagingStatementTemplate =
                        Regex.Replace (
                            pagingStatementTemplate,
                            Common.Constants.UserDefinedName,
                            entity.Name );
                    pagingStatementTemplate =
                        Regex.Replace (
                            pagingStatementTemplate,
                            Common.Constants.UserDefinedParameter,
                            GenerateParameters (
                                true,
                                entity.Metadata.Properties.CountOfPrimaryKeyProperties,
                                entity.Metadata.Properties.PrimaryKeyProperties ) );
                    pagingStatementTemplate =
                        Regex.Replace (
                            pagingStatementTemplate,
                            Common.Constants.UserDefinedPrimaryKey,
                            GenerateConditionClause (
                                true,
                                entity.Metadata.Properties.CountOfPrimaryKeyProperties,
                                entity.Metadata.Properties.PrimaryKeyProperties ) );
                    pagingStatementTemplate =
                        Regex.Replace (
                            pagingStatementTemplate,
                            Common.Constants.UserDefinedTemporaryTableColumn,
                            GenerateTemporaryTableColumn (
                                entity.Metadata.Properties.Count,
                                entity.Metadata.Properties ) );

                    string firstPrimaryKeyPropertyName =
                        entity.Metadata.Properties.PrimaryKeyProperties[ 0 ].Name;
                    pagingStatementTemplate =
                        Regex.Replace (
                            pagingStatementTemplate,
                            Common.Constants.UserDefinedOrderBy,
                            "[" + firstPrimaryKeyPropertyName + "]" );
                    pagingStatementTemplate =
                        Regex.Replace (
                            pagingStatementTemplate,
                            Common.Constants.UserDefinedPagingCondition,
                            "[" + firstPrimaryKeyPropertyName + "] >= @firstID" );
                    pagingStatementTemplate =
                        Regex.Replace (
                            pagingStatementTemplate,
                            Common.Constants.UserDefinedPagingSelect,
                            "@firstID = [" + firstPrimaryKeyPropertyName + "]" );
                }

                return pagingStatementTemplate;
            }

            private static string GenerateParameters (
                bool isNullable,
                int numberOfProperties, 
                PropertyCollection<Metadata> properties )
            {
                StringBuilder result = new StringBuilder ();

                numberOfProperties -= 1;
                for ( int index = 0; index <= numberOfProperties; ++index )
                {
                    Property<Metadata> property = properties[ index ];
                    result.Append ( "  @" );
                    result.Append ( property.Name );
                    result.Append ( " " );
                    result.Append ( property.GetParameterType () );
                    if ( isNullable
                        || property.IsNullable )
                    {
                        result.Append ( " = NULL " );
                    }
                    if ( index < numberOfProperties
                        && numberOfProperties != 0 )
                    {
                        result.Append ( ",\r\n" );
                    }
                }

                return result.ToString ();
            }

            private static string GenerateSelectStatement ( Entity entity )
            {
                string selectStatementTemplate = string.Empty;

                selectStatementTemplate =
                    Regex.Replace (
                        Common.TemplateForDatabaseSelect,
                        Common.Constants.UserDefinedClassName,
                        entity.ClassName );
                selectStatementTemplate =
                    Regex.Replace (
                        selectStatementTemplate,
                        Common.Constants.UserDefinedColumn,
                        GenerateColumns (
                            true,
                            entity.Metadata.Properties.Count,
                            entity.Metadata.Properties ) );
                selectStatementTemplate =
                    Regex.Replace (
                        selectStatementTemplate,
                        Common.Constants.UserDefinedName,
                        entity.Name );
                selectStatementTemplate =
                    Regex.Replace (
                        selectStatementTemplate,
                        Common.Constants.UserDefinedParameter,
                        GenerateParameters (
                            true,
                            entity.Metadata.Properties.CountOfPrimaryKeyProperties,
                            entity.Metadata.Properties.PrimaryKeyProperties ) );
                selectStatementTemplate =
                    Regex.Replace (
                        selectStatementTemplate,
                        Common.Constants.UserDefinedPrimaryKey,
                        GenerateConditionClause (
                            true,
                            entity.Metadata.Properties.CountOfPrimaryKeyProperties,
                            entity.Metadata.Properties.PrimaryKeyProperties ) );

                return selectStatementTemplate;
            }

            private static string GenerateTemporaryTableColumn (
                int numberOfProperties,
                PropertyCollection<Metadata> properties )
            {
                StringBuilder result = new StringBuilder ();

                numberOfProperties -= 1;
                for ( int index = 0; index <= numberOfProperties; ++index )
                {
                    Property<Metadata> property = properties[ index ];
                    result.Append ( "    [" );
                    result.Append ( property.Name );
                    result.Append ( "] " );
                    result.Append ( property.GetParameterType () );
                    if ( index < numberOfProperties
                        && numberOfProperties != 0 )
                    {
                        result.Append ( ",\r\n" );
                    }
                }

                return result.ToString ();
            }

            private static string GenerateUpdateStatement ( Entity entity )
            {
                string updateStatementTemplate = string.Empty;

                updateStatementTemplate =
                    Regex.Replace (
                        Common.TemplateForDatabaseUpdate,
                        Common.Constants.UserDefinedClassName,
                        entity.ClassName );
                updateStatementTemplate =
                    Regex.Replace (
                        updateStatementTemplate,
                        Common.Constants.UserDefinedPrimaryKey,
                        GenerateConditionClause (
                            false,
                            entity.Metadata.Properties.CountOfPrimaryKeyProperties,
                            entity.Metadata.Properties.PrimaryKeyProperties ) );
                updateStatementTemplate =
                    Regex.Replace (
                        updateStatementTemplate,
                        Common.Constants.UserDefinedExpression,
                        GenerateExpressions (
                            entity.Metadata.Properties.CountOfNonPrimaryKeyProperties,
                            entity.Metadata.Properties.NonPrimaryKeyProperties ) );
                updateStatementTemplate =
                    Regex.Replace (
                        updateStatementTemplate,
                        Common.Constants.UserDefinedName,
                        entity.Name );
                updateStatementTemplate =
                    Regex.Replace (
                        updateStatementTemplate,
                        Common.Constants.UserDefinedParameter,
                        GenerateParameters (
                            false,
                            entity.Metadata.Properties.Count,
                            entity.Metadata.Properties ) );

                return updateStatementTemplate;
            }

            private static string GenerateValues (
                int numberOfProperties,
                PropertyCollection<Metadata> properties )
            {
                StringBuilder result = new StringBuilder ();

                numberOfProperties -= 1;
                for ( int index = 0; index <= numberOfProperties; ++index )
                {
                    Property<Metadata> property = properties[ index ];
                    result.Append ( "         @" );
                    result.Append ( property.Name );
                    if ( index < numberOfProperties
                        && numberOfProperties != 0 )
                    {
                        result.Append ( ",\r\n" );
                    }
                }

                return result.ToString ();
            }
        }
    }
}